package com.ningxia.water.mapper;

import com.ningxia.water.entity.TsDayData;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;

import com.ningxia.water.entity.TsMonthData;
import com.ningxia.water.entity.TsWellWarn;
import com.ningxia.water.vo.in.ApplicantWarnVo;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.mapping.FetchType;

import java.util.List;

/**
 * <p>
 * Mapper 接口
 * </p>
 *
 * @author hz
 * @since 2021-03-15
 */
public interface TsDayDataMapper extends BaseMapper<TsDayData> {


    /**
     * 每天用水量总额
     *
     * @param day_time
     * @return
     */
    @Select("select sum(day_data)/10000 as totalvolume from ts_day_data where time = #{day_time}")
    Double getDayTotalVolume(@Param("day_time") String day_time);

    /**
     * 全年用水量统计
     */
    @Select("select sum(day_data)/10000 as cumulativeTotalCount from ts_day_data where time BETWEEN #{startTime} AND #{endTime}")
    Double getYearCumulativeTotalCount(String startTime, String endTime);


    @Select("select well_id,sum(day_data)/10000 as month_data from ts_day_data where time LIKE '${time}%' GROUP BY well_id")
    List<TsMonthData> getSumMonthData(String time);

    /**
     * 根据机井ID和时间范围来查找累计用水量
     *
     * @param well_id
     * @param startTime
     * @param endTime
     * @return
     */
    @Select("SELECT sum(day_data)/10000 as totalVolume from ts_day_data where well_id = #{well_id} and time BETWEEN #{startTime} and #{endTime}")
    Double findVolumeByWellIdAndTime(Integer well_id, String startTime, String endTime);

    /**
     * 根据机井ID和具体时间来查找当天用水量
     *
     * @param well_id
     * @param time
     * @return
     */
    @Select("SELECT sum(day_data)/10000 as totalVolume from ts_day_data where well_id = #{well_id} and time = #{time}")
    Double findVolumeByWellIdAndDay(Integer well_id, String time);

    /**
     * 水量超额报警--找到
     *
     * @param year
     * @return
     */
    @Select("SELECT\n" +
            "\tapplicant_id,\n" +
            "\tsum( plan_volume ) AS planVolume \n" +
            "FROM\n" +
            "\tts_applicant_plan \n" +
            "WHERE\n" +
            "\tplan_year = #{year} \n" +
            "GROUP BY\n" +
            "\tapplicant_id")
    List<ApplicantWarnVo> getApplicantPlanVolumeByYear(String year);

    /**
     * 获取用户从今年第一天到现在的累计水量
     * @param applicantId
     * @param startTime
     * @param endTime
     * @return
     */
    @Select("SELECT\n" +
            "\tsum(dd.day_data) as totalVolume\n" +
            "FROM\n" +
            "\tts_day_data dd\n" +
            "\tLEFT JOIN ts_applicant_well aw ON dd.well_id = aw.well_id\n" +
            "WHERE\n" +
            "\tdd.time BETWEEN #{startTime} AND #{endTime} and aw.applicant_id = #{applicantId}\n" +
            "group by aw.applicant_id")
    Double findTotalVolumesByApplicantId(Integer applicantId, String startTime, String endTime);
}
